/**
 * 
 */
package com.base.myproject.client.busi.service.assessment;

import java.util.ArrayList;
import java.util.List;

import com.base.myproject.client.busi.BaseStore;
import com.base.myproject.client.busi.JDBCModelData;
import com.base.myproject.client.jdbc.StoreInfo;
import com.base.myproject.client.tools.Debug;
import com.base.myproject.client.ui.BaseGrid;
import com.base.myproject.client.ui.celleditor.CellEditorFactory;
import com.extjs.gxt.ui.client.Style.SelectionMode;
import com.extjs.gxt.ui.client.data.ModelType;
import com.extjs.gxt.ui.client.event.ButtonEvent;
import com.extjs.gxt.ui.client.event.SelectionListener;
import com.extjs.gxt.ui.client.widget.ContentPanel;
import com.extjs.gxt.ui.client.widget.HorizontalPanel;
import com.extjs.gxt.ui.client.widget.Html;
import com.extjs.gxt.ui.client.widget.MessageBox;
import com.extjs.gxt.ui.client.widget.VerticalPanel;
import com.extjs.gxt.ui.client.widget.button.Button;
import com.extjs.gxt.ui.client.widget.form.SimpleComboBox;
import com.extjs.gxt.ui.client.widget.form.TextField;
import com.extjs.gxt.ui.client.widget.grid.CheckBoxSelectionModel;
import com.extjs.gxt.ui.client.widget.grid.ColumnConfig;
import com.extjs.gxt.ui.client.widget.grid.ColumnModel;
import com.google.gwt.user.client.ui.Label;
import com.google.gwt.user.client.ui.Widget;

/**
 * 对应AssessmentPanel考核查询的明细查询，
 * 
 * @author Administrator
 *2010-2-3
 */
public class AssessmentQueryDetail extends ContentPanel {
	BaseStore<JDBCModelData> store;
	CellEditorFactory celleditorfactory = new CellEditorFactory();
	SimpleComboBox<String> year;
	SimpleComboBox<String> month;
	TextField<String> bcode = new TextField<String>();
	
	public AssessmentQueryDetail(String year,String month,String name)
	{
		this();
		this.year.setValue(this.year.findModel(year));
		this.month.setValue(this.month.findModel(month));
		bcode.setValue(name);
		
		StoreInfo info  = store.getStoreinfo();;
		info.setSql(initreloadparam());
		store.setStoreinfo(info);
		store.reload();
	}
	public AssessmentQueryDetail()
	{
		init();
		
	}
	void init() {
		year = celleditorfactory.getYEARCombobox();
		month = celleditorfactory.getMONTHCombobox();
		month.setAllowBlank(false);
		year.setAllowBlank(false);
		bcode.setAllowBlank(false);
		setWidth("760px");
		setHeading("员工绩效查询");
		initstore();
		
		initWidget();
	}
	
	void initstore() {
		ModelType type = new ModelType();
		type.setRoot("records");
		type.setRecordName("record");
		type.addField("bcode");

		type.addField("assessrate");
		type.addField("method");
		type.addField("total");
		type.addField("realtotal");
		type.addField("method1");
		
		StoreInfo info = new StoreInfo();
		
		info.setTablename("notable");
		info.setNeedAllRow("no");
		info.setForcenolimit("true");
		
	
		info.setSql(initreloadparam());
	
		store = new BaseStore<JDBCModelData>(type, info,false);
	}
	void initWidget() {
		VerticalPanel vpanel = new VerticalPanel();
		
		vpanel.add(new Html("<br>"));
		vpanel.add(initQuery());
		vpanel.add(new Html("<br>"));
		vpanel.add(CreateGrid());
		
		add(vpanel);
	}
	
	Widget initQuery()
	{
		HorizontalPanel hpanel = new HorizontalPanel();
		hpanel.add(new Label("考核 年："));
		
		hpanel.add(year);
		
		
		hpanel.add(new Label("月："));
	
		hpanel.add(month);
		
		hpanel.add(new Label("人员："));
		
		hpanel.add(bcode);
		
		Button query = new Button("查询");
		hpanel.add(query);
		query.addSelectionListener(new SelectionListener<ButtonEvent>(){

			@Override
			public void componentSelected(ButtonEvent ce) {
				Debug.printf(year.getValue().getValue());
				Debug.printf(month.getSimpleValue());
				
				if(year.isValid() && month.isValid() && bcode.isValid())
				{
				StoreInfo info  = store.getStoreinfo();;
				info.setSql(initreloadparam());
				store.setStoreinfo(info);
				store.reload();
				}
				else
				{
					MessageBox.alert("警告", "请先录入 年，月", null);
				}
			}
			
		});
		return hpanel;
	}
	Widget CreateGrid()
	{
		CheckBoxSelectionModel cbs = new CheckBoxSelectionModel();
		List<ColumnConfig> columns = new ArrayList<ColumnConfig>();
		columns.add(cbs.getColumn());
	
		columns.add(new ColumnConfig("bcode", "名字", 50));
		//columns.add(new ColumnConfig("email", "会员邮箱", 50));
		columns.add(new ColumnConfig("assessrate", "提成", 100));
		columns.add(new ColumnConfig("method", "方式", 100));
		columns.add(new ColumnConfig("total", "应收金额", 100));
		columns.add(new ColumnConfig("realtotal", "实收金额", 100));
		columns.add(new ColumnConfig("method1", "考核办法", 100));
		ColumnModel cm = new ColumnModel(columns);
		cbs.setSelectionMode(SelectionMode.SINGLE);
		BaseGrid  grid = new BaseGrid(store, cm,false,false,null);
		grid.setSelectionModel(cbs);

		grid.setSize(760,300);
		grid.getView().setForceFit(true);
		return grid;
	}
	/**
	 * 考核明细的sql是根据 AssessmentPanel改编而成，AssessmentPanel改sql多数这里也要改
	 * @see AssessmentPanel
	 * @return
	 */
	
	String initreloadparam()
	{
		

		java.lang.StringBuffer sbf = new StringBuffer();
		sbf.append("  select * from        "); 
		sbf.append("  ("); 
		sbf.append("  select a.bcode as bcode,       "); 
		sbf.append("   "); 
		sbf.append("   "); 
		sbf.append("  case       "); 
		sbf.append("  when f.bcode is not null       "); 
		sbf.append("  then       "); 
		sbf.append("  	case when f.methodse='实收比例'        "); 
		sbf.append("  	then     "); 
		sbf.append("  		case when method='指定'    "); 
		sbf.append("  			then f.method1 * realtotal         "); 
		sbf.append("  		when method='轮牌'         "); 
		sbf.append("  			then f.method2 * realtotal         "); 
		sbf.append("  		when method='开发'         "); 
		sbf.append("  			then f.method3 * realtotal         "); 
		sbf.append("  	end      "); 
		sbf.append("  	when f.methodse='应收比例'   "); 
		sbf.append("  	then     "); 
		sbf.append("  		case when method='指定'    "); 
		sbf.append("  			then f.method1 * total   "); 
		sbf.append("  		when method='轮牌'         "); 
		sbf.append("  			then f.method2 * total   "); 
		sbf.append("  		when method='开发'         "); 
		sbf.append("  			then f.method3 * total   "); 
		sbf.append("  	end      "); 
		sbf.append("  	when f.methodse='固定金额'   "); 
		sbf.append("  	then     "); 
		sbf.append("  		case when method='指定'    "); 
		sbf.append("  			then f.method1 "); 
		sbf.append("  		when method='轮牌'         "); 
		sbf.append("  			then f.method2 "); 
		sbf.append("  		when method='开发'         "); 
		sbf.append("  			then f.method3 "); 
		sbf.append("  	end      "); 
		sbf.append("  	end      "); 
		sbf.append("  when h.systemid is not null    "); 
		sbf.append("  then       "); 
		sbf.append("  	case when h.methodse='实收比例'        "); 
		sbf.append("  	then     "); 
		sbf.append("  		case when method='指定'    "); 
		sbf.append("  			then h.method1 * realtotal         "); 
		sbf.append("  		when method='轮牌'         "); 
		sbf.append("  			then h.method2 * realtotal         "); 
		sbf.append("  		when method='开发'         "); 
		sbf.append("  			then h.method3 * realtotal         "); 
		sbf.append("  	end      "); 
		sbf.append("  	when h.methodse='应收比例'   "); 
		sbf.append("  	then     "); 
		sbf.append("  		case when method='指定'    "); 
		sbf.append("  			then h.method1 * total   "); 
		sbf.append("  		when method='轮牌'         "); 
		sbf.append("  			then h.method2 * total   "); 
		sbf.append("  		when method='开发'         "); 
		sbf.append("  			then h.method3 * total   "); 
		sbf.append("  	end      "); 
		sbf.append("  	when h.methodse='固定金额'   "); 
		sbf.append("  	then     "); 
		sbf.append("  		case when method='指定'    "); 
		sbf.append("  			then h.method1 "); 
		sbf.append("  		when method='轮牌'         "); 
		sbf.append("  			then h.method2 "); 
		sbf.append("  		when method='开发'         "); 
		sbf.append("  			then h.method3 "); 
		sbf.append("  	end      "); 
		sbf.append("  	end      "); 
		sbf.append("  when e.service is not null     "); 
		sbf.append("  then       "); 
		sbf.append("  	case when e.methodse='实收比例'        "); 
		sbf.append("  	then     "); 
		sbf.append("  		case when method='指定'    "); 
		sbf.append("  			then e.method1 * realtotal         "); 
		sbf.append("  		when method='轮牌'         "); 
		sbf.append("  			then e.method2 * realtotal         "); 
		sbf.append("  		when method='开发'         "); 
		sbf.append("  			then e.method3 * realtotal         "); 
		sbf.append("  	end      "); 
		sbf.append("  	when e.methodse='应收比例'   "); 
		sbf.append("  	then     "); 
		sbf.append("  		case when method='指定'    "); 
		sbf.append("  			then e.method1 * total   "); 
		sbf.append("  		when method='轮牌'         "); 
		sbf.append("  			then e.method2 * total   "); 
		sbf.append("  		when method='开发'         "); 
		sbf.append("  			then e.method3 * total   "); 
		sbf.append("  	end      "); 
		sbf.append("  	when e.methodse='固定金额'   "); 
		sbf.append("  	then     "); 
		sbf.append("  		case when method='指定'    "); 
		sbf.append("  			then e.method1 "); 
		sbf.append("  		when method='轮牌'         "); 
		sbf.append("  			then e.method2 "); 
		sbf.append("  		when method='开发'         "); 
		sbf.append("  			then e.method3 "); 
		sbf.append("  	end      "); 
		sbf.append("  	end      "); 
		sbf.append("  when c.systemid is not null    "); 
		sbf.append("  then       "); 
		sbf.append("  	case when c.methodse='实收比例'        "); 
		sbf.append("  	then     "); 
		sbf.append("  		case when method='指定'    "); 
		sbf.append("  			then c.method1 * realtotal         "); 
		sbf.append("  		when method='轮牌'         "); 
		sbf.append("  			then c.method2 * realtotal         "); 
		sbf.append("  		when method='开发'         "); 
		sbf.append("  			then c.method3 * realtotal         "); 
		sbf.append("  	end      "); 
		sbf.append("  	when c.methodse='应收比例'   "); 
		sbf.append("  	then     "); 
		sbf.append("  		case when method='指定'    "); 
		sbf.append("  			then c.method1 * total   "); 
		sbf.append("  		when method='轮牌'         "); 
		sbf.append("  			then c.method2 * total   "); 
		sbf.append("  		when method='开发'         "); 
		sbf.append("  			then c.method3 * total   "); 
		sbf.append("  	end      "); 
		sbf.append("  	when c.methodse='固定金额'   "); 
		sbf.append("  	then     "); 
		sbf.append("  		case when method='指定'    "); 
		sbf.append("  			then c.method1 "); 
		sbf.append("  		when method='轮牌'         "); 
		sbf.append("  			then c.method2 "); 
		sbf.append("  		when method='开发'         "); 
		sbf.append("  			then c.method3 "); 
		sbf.append("  	end      "); 
		sbf.append("  	end      "); 
		sbf.append("   "); 
		sbf.append("  when g.systemid is not null    "); 
		sbf.append("  then       "); 
		sbf.append("  	case when g.methodse='实收比例'        "); 
		sbf.append("  	then     "); 
		sbf.append("  		case when method='指定'    "); 
		sbf.append("  			then g.method1 * realtotal         "); 
		sbf.append("  		when method='轮牌'         "); 
		sbf.append("  			then g.method2 * realtotal         "); 
		sbf.append("  		when method='开发'         "); 
		sbf.append("  			then g.method3 * realtotal         "); 
		sbf.append("  	end      "); 
		sbf.append("  	when g.methodse='应收比例'   "); 
		sbf.append("  	then     "); 
		sbf.append("  		case when method='指定'    "); 
		sbf.append("  			then g.method1 * total   "); 
		sbf.append("  		when method='轮牌'         "); 
		sbf.append("  			then g.method2 * total   "); 
		sbf.append("  		when method='开发'         "); 
		sbf.append("  			then g.method3 * total   "); 
		sbf.append("  	end      "); 
		sbf.append("  	when g.methodse='固定金额'   "); 
		sbf.append("  	then     "); 
		sbf.append("  		case when method='指定'    "); 
		sbf.append("  			then g.method1 "); 
		sbf.append("  		when method='轮牌'         "); 
		sbf.append("  			then g.method2 "); 
		sbf.append("  		when method='开发'         "); 
		sbf.append("  			then g.method3 "); 
		sbf.append("  	end      "); 
		sbf.append("  	end      "); 
		sbf.append("   "); 
		sbf.append("   "); 
		sbf.append("  when d.systemid is not null    "); 
		sbf.append("  then       "); 
		sbf.append("  	case when d.methodse='实收比例'        "); 
		sbf.append("  	then     "); 
		sbf.append("  		case when method='指定'    "); 
		sbf.append("  			then d.method1 * realtotal         "); 
		sbf.append("  		when method='轮牌'         "); 
		sbf.append("  			then d.method2 * realtotal         "); 
		sbf.append("  		when method='开发'         "); 
		sbf.append("  			then d.method3 * realtotal         "); 
		sbf.append("  	end      "); 
		sbf.append("  	when d.methodse='应收比例'   "); 
		sbf.append("  	then     "); 
		sbf.append("  		case when method='指定'    "); 
		sbf.append("  			then d.method1 * total   "); 
		sbf.append("  		when method='轮牌'         "); 
		sbf.append("  			then d.method2 * total   "); 
		sbf.append("  		when method='开发'         "); 
		sbf.append("  			then d.method3 * total   "); 
		sbf.append("  	end      "); 
		sbf.append("  	when d.methodse='固定金额'   "); 
		sbf.append("  	then     "); 
		sbf.append("  		case when method='指定'    "); 
		sbf.append("  			then d.method1 "); 
		sbf.append("  		when method='轮牌'         "); 
		sbf.append("  			then d.method2 "); 
		sbf.append("  		when method='开发'         "); 
		sbf.append("  			then d.method3 "); 
		sbf.append("  	end      "); 
		sbf.append("  	end      "); 
		sbf.append("   "); 
		sbf.append("   "); 
		sbf.append("   "); 
		sbf.append("  end  as assessrate ,method ,total,realtotal,'按照比例绩效' as method1  "); 
		sbf.append("   "); 
		sbf.append("   from      "); 
		sbf.append("  ("); 
		sbf.append("  select bcode,method,serviceitem,total, realtotal from        "); 
		sbf.append("  ("); 
		sbf.append("  select bcode800 as bcode ,method800 as method,serviceitem,total,realtotal,upicode from s_service_po_g          "); 
		sbf.append("  	union all          "); 
		sbf.append("   "); 
		sbf.append("  select bcode801 as bcode,method801  as method,serviceitem,total,realtotal,upicode from s_service_po_g          "); 
		sbf.append("  	union all          "); 
		sbf.append("  select bcode802 as bcode,method802 as method ,serviceitem,case when bcode810 is null  then total  when bcode810 ='' then total else total/2 end as total,case when bcode810 is null  then realtotal  when bcode810 ='' then realtotal else realtotal/2 end as realtotal,upicode from s_service_po_g");  
		sbf.append("  	union all          ");
		sbf.append("   ");
		sbf.append("  select bcode810 as bcode,method810 as method ,serviceitem,case when bcode802 is null  then total  when bcode802 ='' then total else total/2 end as total,case when bcode802 is null  then realtotal  when bcode802 ='' then realtotal else realtotal/2 end as realtotal,upicode from s_service_po_g"); 
		sbf.append("  	union all          ");
		sbf.append("  select bcode900 as bcode,method900 as method,serviceitem,total,realtotal,upicode from s_service_po_g ");
		sbf.append("  ) as a     ");
		sbf.append("  where  exists        ");
		sbf.append("  (");
		sbf.append("   ");
		sbf.append("  select * from s_service_po where datepart(year,serviceenddate) ="+year.getSimpleValue()+" and datepart(month,serviceenddate)="+month.getSimpleValue()+" and a.upicode = s_service_po.icode     ");
		sbf.append("  )");
		sbf.append("  and        ");
		sbf.append("    exists ( select * from assessment_bcode_tbl_g where assessment_bcode_tbl_g.bcode = a.bcode and methodicode='按照比例绩效' and assessment_bcode_tbl_g.bcode  in (select bcode from bcode where bname='"+bcode.getValue()+"')");
		sbf.append(" ) ");
		sbf.append("   ");
		sbf.append("  and a.bcode is not null        ");
		sbf.append("   ");
		sbf.append("  ) as a     ");
		sbf.append("   ");
		sbf.append("  left join  ");
		sbf.append("  (");
		sbf.append("  select bcode,systemid,groupicode from bcode        ");
		sbf.append("  ) as b     ");
		sbf.append("  on a.bcode=b.bcode   ");
		sbf.append("   ");
		sbf.append("   ");
		sbf.append("  left join  ");
		sbf.append("  (");
		sbf.append("  select systemid,bcode,service,max(method1) as method1,max(method2) as method2,   ");
		sbf.append("  max(method3) as method3,max(methodse) as methodse from assessment  group by bcode,systemid,service,methodse    ");
		sbf.append("  ) as f     ");
		sbf.append("  on a.serviceitem =f.service and  b.systemid = f.systemid and a.bcode = f.bcode   ");
		sbf.append("   ");
		sbf.append("   ");
		sbf.append("  left join  ");
		sbf.append("  (");
		sbf.append("  select bcode,systemid,max(method1) as method1,max(method2) as method2, ");
		sbf.append("  max(method3) as method3,max(methodse) as methodse from assessment where service is null or service='' group by bcode,systemid,methodse       ");
		sbf.append("  ) as c     ");
		sbf.append("  on a.bcode=c.bcode and b.systemid = c.systemid     ");
		sbf.append("   ");
		sbf.append("   ");
		sbf.append("   ");
		sbf.append("  left join  ");
		sbf.append("  (");
		sbf.append("  select systemid, service,max(method1) as method1,max(method2) as method2,        ");
		sbf.append("  max(method3) as method3,max(methodse) as methodse from assessment where bcode is null or bcode='' group by service,systemid,methodse         ");
		sbf.append("  ) as e     ");
		sbf.append("  on a.serviceitem =e.service and  b.systemid = e.systemid     ");
		sbf.append("   ");
		sbf.append("   ");
		sbf.append("  left join  ");
		sbf.append("  (");
		sbf.append("  select systemid,max(method1) as method1,max(method2) as method2,       ");
		sbf.append("  max(method3) as method3,max(methodse) as methodse from assessment where (bcode is null or bcode='') and (service is null or service='') group by systemid,methodse         ");
		sbf.append("  ) as d     ");
		sbf.append("  on b.systemid=d.systemid       ");
		sbf.append("   ");
		sbf.append("   ");
		sbf.append("  left join  ");
		sbf.append("  (");
		sbf.append("  select systemid,groupicode,max(method1) as method1,max(method2) as method2,      ");
		sbf.append("  max(method3) as method3,max(methodse) as methodse from assessment where (bcode is null or bcode='') and (service is null or service='') group by systemid,groupicode,methodse        ");
		sbf.append("  ) as g     ");
		sbf.append("  on b.systemid=g.systemid and b.groupicode = g.groupicode     ");
    sbf.append("   ");
		sbf.append("  left join  ");
		sbf.append("  (");
		sbf.append("  select systemid,groupicode,service,max(method1) as method1,max(method2) as method2,        ");
		sbf.append("  max(method3) as method3,max(methodse) as methodse from assessment where (bcode is null or bcode='') group by systemid,groupicode,service,methodse      ");
		sbf.append("  ) as h     ");
		sbf.append("  on b.systemid=h.systemid and b.groupicode = h.groupicode and a.serviceitem = h.service     ");
		sbf.append("   ");
		sbf.append("   ");
		sbf.append("  union all  ");
		sbf.append("   ");
		sbf.append("  select bcode, cast (paid*      ");
		sbf.append("  (");
		sbf.append("  select top 1 method from assessment_rule2g where a.paid/area >1 order by a.paid/area asc   ");
		sbf.append("  ) as money) as assessrate ,'' as method ,paid as total,paid as realtotal,'按照区间绩效' as method1 from        ");
		sbf.append("  (");
		sbf.append("  select bcode ,sum(paid) paid from s_service_po     ");
		sbf.append("   ");
		sbf.append("  where exists ( select * from assessment_bcode_tbl_g where assessment_bcode_tbl_g.bcode = s_service_po.bcode and methodicode='按照区间绩效' and assessment_bcode_tbl_g.bcode in (select bcode from bcode where bname='"+bcode.getValue()+"') )    ");
		sbf.append("  and datepart(year,serviceenddate) ="+year.getSimpleValue()+" and datepart(month,serviceenddate)="+month.getSimpleValue()+"         ");
		sbf.append("  group by bcode       ");
		sbf.append("  ) as a     ");
		sbf.append("   ");
		sbf.append("  ) as z     ");
        

		Debug.printf(sbf);
		return ""+sbf;
	}
}
